﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Web;

//添加包含数据库相关类
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ADO
{
    /// <summary>
    ///DataBaseAccess 的摘要说明
    /// </summary>
    public class DataBaseAccess
    {
        // 连接数据源
        private SqlConnection conn = null;
        /// <summary>
        /// 数据源
        /// </summary>
        /// <param name="conStr">数据源连接字符串</param>
        public DataBaseAccess(string conStr)
        {
            conn = new SqlConnection(conStr);
        }
        public DataBaseAccess()
        {
            conn = new SqlConnection("Server=localhost;user id=sa;password=;initial catalog=webinf;Connect Timeout=30");
        }
        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                //OleDbCommand cmd = new OleDbCommand(sql, conn);
                //cmd.CommandTimeout = 20;
                if (this.Open())
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql,conn);
                    adapter.Fill(ds, "tempTable");
                }
            }
            catch (Exception e)
            {
                ds = null;
            }
            finally
            {
                this.Close();
            }
            return ds;
        }
        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sRecord">开始记录数</param>
        /// <param name="mRecord">最大记录数</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sql, int sRecord, int mRecord)
        {
            DataSet ds = new DataSet();
            try
            {
                //OleDbCommand cmd = new OleDbCommand(sql, conn);
                //cmd.CommandTimeout = 20;
                SqlDataAdapter adapter = new SqlDataAdapter(sql,conn);
                adapter.Fill(ds, sRecord, mRecord, "tempTable");
            }
            catch (Exception e)
            {
                ds = null;
            }
            finally
            {
                this.Close();
            }
            return ds;
        }
        /// <summary>
        /// 对数据库的增，删，改的操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否成功</returns>
        public bool ExecuteDataBase(string sql)
        {
            bool succeed = false;
            int cnt = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 20;
                if (this.Open())
                    cnt = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                e.ToString();
            }
            finally
            {
                if (cnt > 0)
                {
                    succeed = true;
                }
                this.Close();
            }
            return succeed;
        }
        /// <summary>
        /// 获得该SQL查询返回的第一行第一列的值，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>返回的第一行第一列的值</returns>
        public string GetScalar(string sql)
        {
            string str = null;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (this.Open())
                    str = cmd.ExecuteScalar().ToString();
            }
            catch (Exception e)
            {
                e.ToString();
            }
            finally
            {
                this.Close();
            }
            return str;
        }

        /// <summary>
        ///  获得该SQL查询返回DataTable，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable tb = null;
            DataSet ds = this.GetDataSet(sql);
            if (ds != null)
            {
                tb = ds.Tables["tempTable"];
            }
            return tb;
        }
        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        private bool Open()
        {
            bool succeed = false;
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                    succeed = true;
                }
                else if (conn.State == System.Data.ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                    succeed = true;
                }
                else if (conn.State == System.Data.ConnectionState.Open)
                {
                    succeed = true;
                }
            }
            catch (Exception e)
            {
                e.ToString();
            }

            return succeed;
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 释放数据库连接资源
        /// </summary>
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
    }
}